__author__ = 'Soul'

from DbOperator import *
from xparser.Record import *
from core.Logger import *

class CarhrDbOperator(DbOperator):
    def __del__(self):
        self.close()

    # insert values into table
    def insert_record(self, record):
        # generate sql to insert data into spd_segment_values
        # generate sql to insert data into spd_segment_names
        (com_seg_name_id, com_seg_val_id) = self.__insert_segment_nv__(record.com_segs)
        (jobinfo_seg_name_id, jobinfo_seg_val_id) = self.__insert_segment_nv__(record.job_info_segs)
        (jobreq_name_id, jobreq_seg_val_id) = self.__insert_segment_nv__(record.job_req_segs)
        (time_seg_name_id, time_seg_val_id) = self.__insert_segment_nv__(record.time_segs)

        # generate sql to insert data into spd_segment
        com_segment_id = self.__insert_segment__(com_seg_name_id, com_seg_val_id, len(record.com_segs))
        job_info_segment_id = self.__insert_segment__(jobinfo_seg_name_id, jobinfo_seg_val_id, len(record.job_info_segs))
        job_req_segment_id = self.__insert_segment__(jobreq_name_id, jobreq_seg_val_id, len(record.job_req_segs))
        time_segment_id = self.__insert_segment__(time_seg_name_id, time_seg_val_id, len(record.time_segs))

        expire = str(record.is_expire_record())
        # generate sql to insert data into spd_records
        self.__insert_record__(record.keyword, record.encode, record.from_url, record.web_name, record.params, com_segment_id, job_info_segment_id, job_req_segment_id, time_segment_id, expire)

#    # query values from table
    def query_record_use_fromurl(self, fromurl, keyword):
        import uuid
        url_uuid = uuid.uuid3(uuid.NAMESPACE_URL, fromurl)
        sql = ("select * from record_view where uuid='%s' and from_url='%s' and keyword='%s' order by record_id DESC limit 1") % (url_uuid, fromurl, keyword)
        one = self.__select_one__(sql)
        if one == None: return None
        return self.__generate_record__(one)

    def __insert_segment_nv__(self, dict):
#        com_segment_id = 1
#        job_info_segment_id = 1
#        job_req_segment_id = 1
#        time_segment_id = 1
#        segment_name_id = 1
#        segment_value_id = 1

        if not len(dict) == 0:
            seg_name_col_ns = 'segment_name_id'
            seg_name_col_vs = 'NULL'
            seg_val_col_ns = 'segment_value_id'
            seg_val_col_vs = 'NULL'
            counter = 1
            for (col_name, col_value) in dict.items():
                seg_name_col_ns = seg_name_col_ns + ", " + 'segment' + str(counter)
                seg_name_col_vs = seg_name_col_vs + ", '" + col_name + "'"

                seg_val_col_ns = seg_val_col_ns + ", " + 'segment' + str(counter)
                seg_val_col_vs = seg_val_col_vs + ", '" + col_value + "'"

            sql1 = "insert into spd_segment_names(" + seg_name_col_ns + ") values(" + seg_name_col_vs + ")"
            sql2 = "insert into spd_segment_values(" + seg_val_col_ns + ") values(" + seg_val_col_vs + ")"
            seg_name_id = self.__do_insert__(sql1)
            seg_val_id = self.__do_insert__(sql2)
            return (seg_name_id, seg_val_id)
        else:
            return (1, 1)

    def __insert_segment__(self, name_id, value_id, num):
        if num == 0:
            return 1
        sql = 'insert into spd_segment(segment_id, segment_name_id, segment_value_id, segment_num) '
        sql = sql + "values(NULL, %d, %d, %d)" % (name_id, value_id, num)
        return self.__do_insert__(sql)

    def __insert_record__(self, keyword, encode, url, web_name, dict, com_segment_id, job_info_segment_id, job_req_segment_id, time_segment_id, expire):
        import uuid
        url_uuid = uuid.uuid3(uuid.NAMESPACE_URL, url)

        col_ns = 'record_id'
        col_vs = 'NULL'
        for col_name, col_value in dict.items():
            try:
                col_ns = col_ns + ', ' + col_name
                col_vs = "%s, '%s'" % (col_vs, col_value)
            except UnicodeDecodeError as e:
                logger.error("UnicodeDecodeError: %s, %s" % (url, web_name))
                logger.error(e)
                return False

        col_ns = col_ns + ', keyword, encode, web_site, from_url, uuid, com_segment_id, job_info_segment_id, job_req_segment_id, time_segment_id, expire'
        col_vs = col_vs + ", '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'" % (keyword , encode, web_name, url, url_uuid, com_segment_id, job_info_segment_id, job_req_segment_id, time_segment_id, expire)

        sql = 'insert into spd_records(' + col_ns + ') values(' + col_vs + ')'
        return self.__do_insert__(sql)

    def __generate_record__(self, res):
        if res == None: return None

        record = Record()
        record.encode = res['encode']
        record.from_url = res['from_url']
        record.web_name = res['web_site']

        record.keyword = res['keyword']

        # for industry, we have special case!
        record.params['com_name'] = res['com_name']
        record.params['com_idsty'] = res['com_idsty']
        record.params['com_type'] = res['com_type']
        record.params['com_size'] = res['com_size']
        record.params['com_loc'] = res['com_loc']

        record.params['job_info_name'] = res['job_info_name']
        record.params['job_info_hirenum'] = res['job_info_hirenum']
        record.params['job_info_base'] = res['job_info_base']
        record.params['job_info_salary'] = res['job_info_salary']
        for j in range(res['jobinfo_segnum']):
            i = j + 1
            name_key = 'job_info_flex' + str(i) + '_name'
            name = 'jobinfo_name' + str(i)
            value_key = 'job_info_flex' + str(i) + '_cntnt'
            value = 'jobinfo_value' + str(i)
            # print "name_key:%s, name:%s, value_key:%s, value:%s" % (name_key, name, value_key, value)
            record.job_info_segs[name_key] = res[name]
            record.job_info_segs[value_key] = res[value]

        record.params['job_rqrmt_edu'] = res['job_rqrmt_edu']
        record.params['job_rqrmt_mjr'] = res['job_rqrmt_mjr']
        record.params['job_rqrmt_expr'] = res['job_rqrmt_expr']
        for j in range(res['jobrqr_segnum']):
            i = j + 1
            name_key = 'job_rqrmt_flex' + str(i) + '_name'
            name = 'jobrqr_name' + str(i)
            value_key = 'job_rqrmt_flex' + str(i) + '_cntnt'
            value = 'jobrqr_value' + str(i)
            # print "name_key:%s, name:%s, value_key:%s, value:%s" % (name_key, name, value_key, value)
            record.job_req_segs[name_key] = res[name]
            record.job_req_segs[value_key] = res[value]

        record.params['job_desc_p'] = res['job_desc_p']

        record.params['time_lupd'] = res['time_lupd']
        record.params['time_pstd'] = res['time_pstd']
        record.params['time_ed'] = res['time_ed']
        record.params['time_effd'] = res['time_effd']

        return record
